/*
 * Copyright (C), 2018-2018, ZCMS
 * FileName: ExcelUtils.java
 * Author:   DINGYONG
 * Date:     2018年4月24日 上午11:47:25
 * Description: //模块目的、功能描述
 * History: //修改记录
 * <author>      <time>      <version>    <desc>
 * 修改人姓名             修改时间            版本号                  描述
 */
package util.dingyong;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;


/**
 * Excel操作类<br>
 * 〈功能详细描述〉
 *
 * @author 阿丁
 * @see [相关类/方法]（可选）
 * @since [产品/模块版本] （可选）
 */
public final class ExcelGenHelper {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelGenHelper.class);

    private static final String preFix = "leo_";
    private static final String preFix4j = "leo";

    // 表集合
    private static List<TableEntity> list = new ArrayList<>();

    public static String ExcelConvertDb(File file) throws Exception {

        // 1、判断Excel是否存在
        if (ExcelGenHelper.isNotExist(file)) {
            LOGGER.info("文件不存在");
            return null;
        }
        String fileName = file.getName();
        // 建立输入流
        InputStream input = new FileInputStream(file);
        Workbook wb = null;
        // 根据文件格式(2003或者2007)来初始化
        if (fileName.endsWith("xlsx")) {
            wb = new XSSFWorkbook(input);
        } else {
            wb = (Workbook) new HSSFWorkbook(input);
        }
        // 遍历表单sheet
        Iterator<Sheet> sheets = wb.sheetIterator();

        // 获取表信息
        list = ExcelGenHelper.getTableInfo4Excel(sheets, true);
        StringBuffer sql = new StringBuffer();

        if (CollectionUtils.isNotEmpty(list)) {
            for (TableEntity tableEntity : list) {
                // 类名
                String tableName = tableEntity.getTableName();
                List<ColumnEntity> tableColumn = tableEntity.getTableColumn();
                if (CollectionUtils.isNotEmpty(tableColumn)) {

                    sql.append("CREATE TABLE '" + tableName + "' (");
                    for (ColumnEntity columnEntity : tableColumn) {

                        // 字段名
                        sql.append("'" + columnEntity.getColumnName() + "'");
                        // 类型
                        sql.append(columnEntity.getColumnType() + "(" + columnEntity.getColumnSize() + ")");
                        // 是否为null
                        if (columnEntity.isNull()) {
                            sql.append("DEFAULT NULL");
                        }
                        // 是否为自增
                        if (columnEntity.isAuto()) {
                            sql.append("AUTO_INCREMENT");
                        }
                        // 备注
                        sql.append("COMMENT '" + columnEntity.getColumnMemo() + "',");

                    }
                    // 主键
                    sql.append("PRIMARY KEY (`id`)");
                    // 结束
                    sql.append(") ENGINE=InnoDB AUTO_INCREMENT=199 DEFAULT CHARSET=utf8;");

                }
            }
        }

        return sql.toString();
    }

    /**
     * 功能描述: 从excel中读取信息转换成java实体类
     *
     * @param file excel文件
     * @param savePath 保存路径
     * @param pack 包名
     */
    public static void ExcelConvertObject(File file, String savePath, String pack) {

        LOGGER.info(" convert object begin ! ");

        try {
            // 1、判断Excel是否存在
            if (ExcelGenHelper.isNotExist(file)) {
                LOGGER.info("文件不存在");
                return;
            }
            // 2、 创建存放路径目录
            File save = new File(savePath);
            if (!save.exists()) {
                save.mkdirs();
            }
            String fileName = file.getName();
            // 建立输入流
            InputStream input = new FileInputStream(file);
            Workbook wb = null;
            // 根据文件格式(2003或者2007)来初始化
            if (fileName.endsWith("xlsx")) {
                wb = new XSSFWorkbook(input);
            } else {
                wb = (Workbook) new HSSFWorkbook(input);
            }
            // 遍历表单sheet
            Iterator<Sheet> sheets = wb.sheetIterator();

            // 获取表信息
            list = ExcelGenHelper.getTableInfo4Excel(sheets, false);
            // 包名
            pack = "package " + pack + ";\n\n";
            if (CollectionUtils.isNotEmpty(list)) {
                for (TableEntity tableEntity : list) {

                    // 类名
                    String ClassName = tableEntity.getTableName();
                    // 字段名
                    StringBuffer classColumnSb = new StringBuffer();

                    List<String> typeList = new ArrayList<String>();
                    List<String> nameList = new ArrayList<String>();

                    List<ColumnEntity> tableColumn = tableEntity.getTableColumn();
                    if (CollectionUtils.isNotEmpty(tableColumn)) {
                        for (ColumnEntity columnEntity : tableColumn) {
                            String columnMemo = columnEntity.getColumnMemo();
                            String columnName = columnEntity.getColumnName();
                            String columnType = columnEntity.getColumnType();
                            // 字段类型转换
                            MysqlUtil.convertType(columnType);
                            // 拼接string
                            sbAppendType4J(classColumnSb, columnMemo, columnType, columnName);

                            typeList.add(columnType);
                            nameList.add(columnName);

                        }
                        // 拼接get/set方法
                        getSet(typeList, nameList, classColumnSb);
                        // 拼接类头
                        StringBuffer sb = new StringBuffer();
                        sbAppendStart4J(sb, pack, ClassName);
                        sb.append(classColumnSb.toString());

                        // 删除旧的类文件
                        File f = new File(savePath + "/" + ClassName + ".java");
                        if (f.exists()) {
                            f.delete();
                        }
                        // 创建文件输出流
                        FileOutputStream fos = new FileOutputStream(f);
                        if (null != fos) {
                            fos.write(sb.toString().getBytes("UTF-8"));
                            fos.close();
                        }
                        LOGGER.info(" ----- convert object: " + ClassName + " end -----  ! ");
                    }
                }
            }

            LOGGER.info(" convert object all end ! ");

        } catch (IOException ex) {

            LOGGER.error(" convert object failue! ", ex);
            ex.printStackTrace();

        }
    }

    /**
     * 获取表名 功能描述:
     *
     * @param row
     * @param tableEntity
     * @param isDb 生成类型 db or java :true:db false:java
     */
    private static void getTableName4Excel(Row row, TableEntity tableEntity, boolean isDb) {
        // 第一行是表名
        int rowNum = row.getRowNum();
        if (rowNum != 0) {
            return;
        }

        Cell tableNameCell = row.getCell(0);// 第一行表名
        String tableName = tableNameCell.getStringCellValue().trim();

        if (isDb) {
            tableEntity.setTableName(tableName);
        } else {
            boolean isTableName = tableName.startsWith(preFix);// 判断是否leo_开头
            if (isTableName) {
                tableEntity.setTableName(ExcelGenHelper.convertString(tableName));
            }
        }

    }

    /**
     * 从sheet中获取表集合 功能描述:
     *
     * @param rows
     * @param isDb 生成类型 db or java true:db false:java
     */
    public static TableEntity getTableInfo4Sheet(Iterator<Row> rows, boolean isDb) {

        TableEntity tableEntity = new TableEntity();
        List<ColumnEntity> tableColumn = new ArrayList<>();
        while (rows.hasNext()) {
            Row row = rows.next();
            if (row.getRowNum() == 0) {
                ExcelGenHelper.getTableName4Excel(row, tableEntity, isDb);
            }
            if (row.getRowNum() > 1) {
                // 从第三行开始 列0,1,6表示属性名，字段类型，字段备注
                Cell cell0 = row.getCell(0);
                Cell cell1 = row.getCell(1);
                Cell cell2 = row.getCell(2);
                Cell cell3 = row.getCell(3);
                Cell cell4 = row.getCell(4);
                Cell cell5 = row.getCell(5);
                String columnName = cell0.getStringCellValue().trim();
                String columnType = cell1.getStringCellValue().trim();
                String columnSize = String.valueOf(cell2.getNumericCellValue());
                boolean isNull = cell3.getBooleanCellValue();
                boolean isAuto = cell4.getBooleanCellValue();
                String columnMemo = cell5.getStringCellValue().trim();

                if (StringUtils.isNotEmpty(columnName)) {
                    ColumnEntity columnEntity = new ColumnEntity();
                    columnEntity.setColumnType(columnType);
                    if (isDb) {
                        columnEntity.setColumnName(columnName);
                    } else {
                        columnEntity.setColumnName(ExcelGenHelper.convertString(columnName));
                    }
                    columnEntity.setColumnSize(columnSize);
                    columnEntity.setAuto(isAuto);
                    columnEntity.setNull(isNull);
                    columnEntity.setColumnMemo(columnMemo);
                    tableColumn.add(columnEntity);
                }
            }
        }
        tableEntity.setTableColumn(tableColumn);
        return tableEntity;
    }

    /**
     * 从excel中获取所有表信息 功能描述:
     *
     * @param sheets
     * @param isDb 转换java类型还是数据库类型
     * @return
     */
    public static List<TableEntity> getTableInfo4Excel(Iterator<Sheet> sheets, boolean isDb) {
        list.clear();
        while (sheets.hasNext()) {
            Sheet sheet = sheets.next();
            Iterator<Row> rows = sheet.rowIterator();
            TableEntity tableEntity = ExcelGenHelper.getTableInfo4Sheet(rows, isDb);
            list.add(tableEntity);
        }
        return list;
    }

    /**
     * 类型转换 功能描述: eg : leo_abc ==> leoAbc
     *
     * @param column
     * @return
     */
    private static String convertString(String column) {
        // 首字母大写
        String columnSuff = column.split(preFix)[1];
        String columnSuffNameSuff = String.valueOf(columnSuff.charAt(0)).toUpperCase() + columnSuff.substring(1);
        column = preFix4j + columnSuffNameSuff;// 转换成类类型
        return column;
    }

    /**
     * 拼接生成类字段 功能描述:
     *
     * @param sbStart
     * @param pack
     * @param className
     */
    private static void sbAppendStart4J(StringBuffer sbStart, String pack, String className) {
        // 拼接输出开始语句
        sbStart.append(pack);
        sbStart.append("/**\n * ");
        sbStart.append(className);
        sbStart.append("\n * \n * @author other\n *\n */\n");
        sbStart.append("public class ");
        sbStart.append(className);
        sbStart.append(" {\n");
    }

    /**
     * 拼接生成类头 功能描述:
     *
     * @param sbType
     * @param columnMemo
     * @param columnType
     * @param columnName
     */
    private static void sbAppendType4J(StringBuffer sbType, String columnMemo, String columnType, String columnName) {
        // 拼接输出语句
        sbType.append("\n");
        sbType.append("\t// ");
        sbType.append(columnMemo);
        sbType.append("\n");
        sbType.append("\tpublic ");
        sbType.append(columnType);
        sbType.append(" ");
        sbType.append(columnName);
        sbType.append(";\n");
    }

    /**
     * 填充Get,Set方法 功能描述:
     *
     * @param typeList
     * @param nameList
     * @param sb
     */
    private static void getSet(List<String> typeList, List<String> nameList, StringBuffer sb) {
        for (int i = 0; i < typeList.size(); i++) {
            // 属性类型和名称
            String type = typeList.get(i);
            String name = nameList.get(i);
            // GET,SET方法首字母大写
            String c = String.valueOf(name.charAt(0)).toUpperCase();
            String n = c + name.substring(1);
            // 拼接输出语句
            sb.append("\n\tpublic ");
            sb.append(type);
            sb.append(" get");
            sb.append(n);
            sb.append("() {");
            sb.append("\n\t\treturn ");
            sb.append(name);
            sb.append(";\n\t}\n");
            sb.append("\n\tpublic void set");
            sb.append(n);
            sb.append("(");
            sb.append(type);
            sb.append(" ");
            sb.append(name);
            sb.append(") {");
            sb.append("\n\t\tthis.");
            sb.append(name);
            sb.append(" = ");
            sb.append(name);
            sb.append(";\n\t}\n");
        }
        sb.append("\n}");
    }

    /**
     * 判断文件是否存在 功能描述:
     *
     * @param file
     * @return
     */
    protected static boolean isExist(File file) {
        if (null == file || !file.exists()) {
            return false;
        }
        return true;
    }

    /**
     * 判断文件是否存在 功能描述:
     *
     * @param file
     * @return
     */
    protected static boolean isNotExist(File file) {
        if (null == file || !file.exists()) {
            return true;
        }
        return false;
    }

    // test
    public static void main(String[] args) throws Exception {
//	ExcelGenHelper.ExcelConvertObject(new File("C:/Users/11734/Desktop/database.xlsx"), "C:/Users/11734/Desktop",
//		"com.leo4j.base.core");

        String sql = ExcelGenHelper.ExcelConvertDb(new File("C:/Users/11734/Desktop/database.xlsx"));
        System.out.println(sql);
    }

}
